import os
import sys
import openpyxl
import pandas
from openpyxl import load_workbook
from openpyxl import worksheet

fliename = sys.argv[1]

wb = load_workbook(fliename,data_only=True)
sheet = wb.sheetnames
ws = wb[sheet[0]]
f = open('检查结果.txt','w')
#ws = wb.active

os.system("taskkill /f /im notepad.exe 1>nul 2>nul")


#Item : Check sheet name
def CSN():
    #Check for extra sheets
    if len(wb.sheetnames) != 1:
        #print("存在多余工作表，请删除！")
        f.write("存在多余工作表，请删除！")
    #Check hidden sheets
    for i in wb.worksheets:
        if i.sheet_state == "hidden":
            #print("存在隐藏工作表！")
            f.write('\n存在隐藏工作表！')


#Item: Check illegal characters
def CIC():
    #All
    #Find 0 value cells and output coordinates
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=1, max_col=93):
        for cell in row:
            if cell.value == 0:
               #print("0值所在单元格：" + cell.coordinate )
               f.write("\n0值所在单元格：" + cell.coordinate )
    #All
    #Find '-' value cells and output coordinates
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=1, max_col=93):
        for cell in row:
            if cell.value == "-":
               #print("单字符“-”所在单元格：" + cell.coordinate)
               f.write("\n单字符“-”所在单元格：" + cell.coordinate)
    #Find ' value cells and output coordinates
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=1, max_col=93):
        for cell in row:
            if str(cell.value).find('\'') != -1:
               #print("带引号的单元格坐标：" + cell.coordinate)
               f.write("\n带引号的单元格坐标：" + cell.coordinate)
            elif str(cell.value).find('’') != -1:
                #print("带引号的单元格坐标：" + cell.coordinate)
                f.write("\n带引号的单元格坐标：" + cell.coordinate)
            elif str(cell.value).find('‘') != -1:
                #print("带引号的单元格坐标：" + cell.coordinate)
                f.write("\n带引号的单元格坐标：" + cell.coordinate)
            elif str(cell.value).find('\'') != -1:
                #print("带引号的单元格坐标：" + cell.coordinate)
                f.write("\n带引号的单元格坐标：" + cell.coordinate)
    #Find formula errors and output coordinates
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=1, max_col=93):
        for cell in row:
            if cell.value == '#N/A':
               #print("公式计算错误所在单元格：" + cell.coordinate)
               f.write("\n公式计算错误所在单元格：" + cell.coordinate)
            elif cell.value == '#REF!':
                #print("公式计算错误所在单元格：" + cell.coordinate)
                f.write("\n公式计算错误所在单元格：" + cell.coordinate)


#Item : : Checking for formatting errors
def CFFE():
    #Find cells that end with spaces and output coordinates
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=3, max_col=93):
        for cell in row:
            if len(str(cell.value)) != 0:
                if (str(cell.value)[-1]).find(' ') != -1:
                   #print("以空格结束的单元格：" + cell.coordinate)
                   f.write("\n以空格结束的单元格：" + cell.coordinate)
    #Check whether the color number area is too long
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=69, max_col=69):
        for cell in row:
            if len(str(cell.value)) > 30:
                #print("色号区域超过30个字符：" + cell.coordinate)
                f.write("\n色号区域超过30个字符：" + cell.coordinate)
    #Find the plate number with suffix space and output coordinates
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=67, max_col=67):
        for cell in row:
            if str(cell.value).find(' ') != -1:
               #print("带空格的板块编号坐标：" + cell.coordinate)
               f.write("\n带空格的板块编号坐标：" + cell.coordinate)
    #Check whether the number of columns is correct
    for row in ws.iter_rows(min_row=4, max_row=4, min_col=1, max_col=100):
        for cell in row:
            if cell.value == "图纸链接":
                if cell.column != 93:
                    #print('表格列数异常，请检查！')
                    f.write('\n表格列数异常，请检查！')


#Item: Numerical anomaly
def NA():
    #column-F
    #Check whether the production quantity is greater than 1
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=6, max_col=6):
        for cell in row:
            if cell.value != None:
                if cell.value != 1:
                    #print("生产数量大于1（此处录mest前修改）：" + cell.coordinate)
                   f.write("\n生产数量大于1（此处录mest前修改）：" + cell.coordinate)
    #column-S
    #Inventory attribute is blank
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=2, max_col=2):
        for cell in row:
            if cell.value != None:
                U = int(cell.row)
                for row in ws.iter_rows(min_row=U, max_row=U, min_col=19, max_col=19):
                    for cell in row:
                        if cell.value != "销售,生产":
                            #print("请确定材料用量或计量单位是否正确：" + cell.coordinate)
                            f.write("\n存货属性一栏为空：" + cell.coordinate)
    #column-BT                          
    #Material usage has null value
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=65, max_col=65):
        for cell in row:
            if cell.value != None:
                U = int(cell.row)
                for row in ws.iter_rows(min_row=U, max_row=U, min_col=72, max_col=72):
                    for cell in row:
                        if cell.value == None:
                            #print("请确定材料用量或计量单位是否正确：" + cell.coordinate)
                            f.write("\n请确定材料用量或计量单位是否正确：" + cell.coordinate)
    #column-BU
    #UOM has null value
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=65, max_col=65):
        for cell in row:
            if cell.value != None:
                U = int(cell.row)
                for row in ws.iter_rows(min_row=U, max_row=U, min_col=73, max_col=73):
                    for cell in row:
                        if cell.value == None:
                            #print("请确定材料用量或计量单位是否正确：" + cell.coordinate)
                            f.write("\n请确定材料用量或计量单位是否正确：" + cell.coordinate)

#Item: Duplicate and missing check
def DAMC():
    #Find duplicate plate numbers and output coordinates（Object must be in text form）
    last_row = ws.max_row
    names = []
    for B in range(6, last_row+1):
        names.append(ws.cell(row=B,column=2).value)
    for row in ws.iter_rows(min_row=6, max_row=30000, min_col=2, max_col=2):
        for cell in row:
            if names.count(str(cell.value)) == 2:
               #print("重复编号所在单元格：" + cell.coordinate)
               f.write("\n重复编号所在单元格：" + cell.coordinate)

#Column by column inspection
def CBCI():
    #column-A(1)
    #Check whether the number is consistent with the plate
    U = int()
    for row in ws.iter_rows(min_row=6+U, max_row=30000, min_col=2, max_col=2):
        for cell in row:
            if cell.value != None:
                W = cell.value
                U = int(cell.row)
                return U
                for row in ws.iter_rows(min_row=U+1, max_row=30000, min_col=1, max_col=1):
                    for cell in row:
                        if cell.value != None:
                            V = int(cell.row)
                            for row in ws.iter_rows(min_row=U, max_row=V-1, min_col=1, max_col=1):
                                if str(cell.value).find(W) != -1:
                                    #print("带空格的板块编号坐标：" + cell.coordinate)
                                    f.write("\n首列错误编号所在单元格：" + cell.coordinate)
                                    break

def Main():
    CSN()
    CIC()
    CFFE()
    NA()
    DAMC()
    CBCI()

Main()

#print("\n以上为检查结果，请按结果在表格内修改。")
f.write("\n以上为检查结果，请按结果在表格内修改。")
f.close()

os.system("notepad 检查结果.txt")

